/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.service.Parameter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class ParameterDAO {

    private static String GET_PARAMETERS = "{ call PKG_CURDS.GET_PARAMETERS (?) }";
    private static String GET_PARAMETERS_NAME = "{ call PKG_CURDS.GET_PARAMETERS_NAME (?, ?) }";
    private DataSource ds;

    public ParameterDAO(DataSource ds) {
        this.ds = ds;
    }

    public List<Parameter> getParameters() throws Exception {
        List<Parameter> retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_PARAMETERS);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(1);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;
                retorno = new ArrayList<Parameter>();
                while (rs.next()) {
                    Parameter item = new Parameter();
                    item.setId(rs.getInt("PARAMETERID"));
                    item.setName(rs.getString("PARAMETERNAME"));
                    item.setType(rs.getString("PARAMETERTYPE"));
                    item.setValue(rs.getString("PARAMETERVALUE"));
                    retorno.add(item);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return retorno;
    }

    public Parameter getParameterByName(String name) throws Exception {
        Parameter retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_PARAMETERS_NAME);
            stmt.setString(1, name);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    retorno = new Parameter();
                    retorno.setId(rs.getInt("PARAMETERID"));
                    retorno.setName(rs.getString("PARAMETERNAME"));
                    retorno.setType(rs.getString("PARAMETERTYPE"));
                    retorno.setValue(rs.getString("PARAMETERVALUE"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
    
    
   
}
